Configure SQL Server

Rock needs its own SQL Server login, a network path to the database and SQL Server Agent running. In this article you'll create that login, turn on TCP/IP, start SQL Server Agent automatically and open the firewall port.

What you'll configure

• A RockUser login with SQL Server authentication and the dbcreator role
• TCP/IP turned on for the SQL Server instance
• SQL Server Agent set to start automatically
• A Windows Defender Firewall rule allowing TCP port 1433

Before you create a login, know which kind of SQL Server you're using:

• Azure SQL Database - A fully managed database service. You create logins with SQL commands in SSMS; those commands are in Create the Rock login (Azure SQL).
• SQL Server on a server you manage - Local or on an Azure virtual machine. Use the screens in Create the Rock login.

Create the Rock login

1. Open SQL Server Management Studio. In Connect, set Server Name to localhost, Authentication to SQL Server Authentication, User Name to sa and enter the sa password you created during installation.

2. Leave Encrypt on Mandatory and check Trust Server Certificate. A new SQL Server uses its own self-signed certificate, so the connection fails without this. Select Connect.

3. In Object Explorer, expand Security, right-click Logins and select New Login....

4. On General, enter RockUser as the login name, choose SQL authentication and enter a password twice.

5. Uncheck Enforce password policy before you select OK. That also clears Enforce password expiration and User must change password at next login. If User must change password at next login stays on, the Rock installer can't sign in.

6. Select Server Roles, check dbcreator and select OK.

Create the Rock login (Azure SQL)

Azure SQL Database creates logins with SQL commands instead of the Login Properties screens. You don't need to know SQL; paste each script as shown.

1. Open SQL Server Management Studio, change Authentication from Windows Authentication to SQL Server Authentication and sign in as sa with the password you created during installation.

2. Expand Databases, then System Databases. Right-click the master database and select New Query.

3. Paste this script into the query window. Replace xxxxxxx with a password, keeping the single quotes. Don't use special characters in this password.

CREATE LOGIN [RockUser]

WITH PASSWORD = 'xxxxxxx';

CREATE USER [RockUser]

FROM LOGIN [RockUser]

WITH DEFAULT_SCHEMA = dbo;

ALTER ROLE dbmanager ADD member [RockUser]

ALTER ROLE loginmanager ADD member [RockUser]
            

4. Select Execute.

5. Finish Turn on TCP/IP and SQL Server Agent and the firewall steps below, then go to Install Rock. The installer builds your database.

6. After the database is built, return to SSMS. Expand Databases, right-click your new Rock database and select New Query. Paste this script and run it:

CREATE USER [RockUser]

FOR LOGIN [RockUser]

WITH DEFAULT_SCHEMA = dbo;

ALTER ROLE db_owner ADD member [RockUser]
            

Turn on TCP/IP and SQL Server Agent

1. Open SQL Server Configuration Manager from the Start menu. Search for "SQL Server Configuration Manager" if you don't see it; the name includes your SQL Server version.

2. Expand SQL Server Network Configuration, select Protocols for MSSQLSERVER, right-click TCP/IP and select Enable. Select OK on the restart notice.

3. Select SQL Server Services, right-click SQL Server Agent (MSSQLSERVER) and select Properties.

4. On the Service tab, set Start Mode to Automatic and select Apply, then OK.

5. Right-click SQL Server (MSSQLSERVER) and select Restart so the TCP/IP change takes effect.

Open the SQL Server port in the firewall

1. In Server Manager, select Tools > Windows Defender Firewall with Advanced Security.

2. Right-click Inbound Rules and select New Rule.... Choose Port and select Next.

3. Choose TCP, select Specific local ports, enter 1433 and select Next.

4. Choose Allow the connection and select Next.

5. Choose the profiles the rule applies to and select Next.

6. Name the rule, for example SQL Server (TCP 1433), and select Finish.

If Rock and SQL Server run on the same server, lock port 1433 down so your database isn't exposed to the internet. Open the rule you just created, go to the Scope tab and add 127.0.0.1 under both Local IP address and Remote IP address. This doesn't apply to an Azure setup.

SQL Server is ready. Next, install Rock.